IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PopulateWorkflowResources]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PopulateWorkflowResources]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/***************************************************************************
* Name:			PopulateWorkflowResources
* Purpose:		Populates the workflow resources table
*				from person tables via GetWorkflowResources() function.
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
Create Procedure PopulateWorkflowResources
AS
Begin

set nocount on

-- Clean out the workflow resources in preparation for re-populating the latest ones.
Truncate table [dbo].[WorkflowResources]

-- Drop indexes on the workflow resources in preparation for re-populating the latest ones.  We need to drop
-- the indexes because they have unique constraints and we will be initially inserting duplicate rows.
if exists(select 1 from sysindexes where name = 'UserId' and id = object_id('dbo.WorkflowResources')) drop index [dbo].[WorkflowResources].[UserId]
if exists(select 1 from sysindexes where name = 'Name' and id = object_id('dbo.WorkflowResources')) drop index [dbo].[WorkflowResources].[Name]

-- Insert into the workflow resource table using the function getworkflowresources.
INSERT INTO [dbo].[WorkflowResources]([UserID],[Name],[FirstName],[LastName],[EmailID],[Department])
select distinct userId, [Name], FirstName, LastName, EmailID, Department 
from dbo.getworkflowresources()

-- Detect duplicate User IDs and save in #DuplicatedUsers temp table.
if object_id('tempdb..#DuplicatedUsers') is not null drop table #DuplicatedUsers
select userid,count(*) as MyCount into #DuplicatedUsers 
from dbo.WorkflowResources group by userid having count(*)>1

-- Eliminate duplicate user IDs by deleting all but the min or max values.
delete w from dbo.WorkflowResources w inner join #DuplicatedUsers dup on dup.userid = w.userid
where w.firstname <> (select min(w1.firstname) from dbo.WorkflowResources w1 where w1.userid = w.userid)
or w.lastname <> (select min(w1.lastname) from dbo.WorkflowResources w1 where w1.userid = w.userid)
or w.emailID <> (select max(w1.emailID) from dbo.WorkflowResources w1 where w1.userid = w.userid)
or w.Department <> (select max(w1.Department) from dbo.WorkflowResources w1 where w1.userid = w.userid)

--Re-create the unique indexes on the workflow resources.
create unique clustered Index UserId on [dbo].[WorkflowResources] (UserId)
create unique Index Name on [dbo].[WorkflowResources] ([Name])

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO